/*
Rules of Metadata Driven Database Deployments for Indexes
	--if an index exists with the same name and same definition, do nothing
	--if an index exists with the same name and different definition, then modify the existing index as necessary
	--if an index exists with a different name but same definition exists on another index, then rename the index
	--otherwise, create the index.  

*/


IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.CreateIndex'))
BEGIN
	DROP PROCEDURE MD3.CreateIndex
END
GO
CREATE PROCEDURE MD3.CreateIndex
(
	@SchemaName			SYSNAME,
	@TableName			SYSNAME,
	@IndexName			SYSNAME,
	@IndexKeyColumns	VARCHAR(MAX),	-- csv list of key columns. Append (-) to column name for DESC
	@IncludedColumns	VARCHAR(MAX) = NULL,
	@FileGroupOrPartition VARCHAR(MAX) = NULL,
	@IsUnique			BIT = 0,
	@IsClustered		BIT = 0,
	@FillFactor			TINYINT = 100,	-- = defaulted to 100 percent as this is what we will want most of the time.
	@PadIndex			BIT = 0,
	@DropIndex			BIT = 0,
	@DataCompressionType VARCHAR(50) = 'ROW'  -- ROW/PAGE/NULL/NONE 
)
AS
BEGIN

	SET NOCOUNT ON

		DECLARE
			@CurrentProcName SYSNAME,
			@CurrentIndexName SYSNAME,
			@CurrentDataSpace VARCHAR(200),
			@CurrentFillFactor INT,
			@CurrentIsPadded BIT,
			@Msg VARCHAR(MAX),
			@SQL VARCHAR(MAX),
			@SQL2 VARCHAR(MAX),
			@IsOnline BIT = 1,
			@DataComprssion VARCHAR(MAX) = '',
			@CON_COUNT INT = 0,
			@CON_SQL VARCHAR(MAX) = '',
			@CONSTRAINTNAME SYSNAME = '',
			@FKSchema SYSNAME,
			@FKTable SYSNAME,
			@FKName SYSNAME,
			@CurrentDataCompressionType VARCHAR(4),
			@DropIdxDataCompressionType VARCHAR(4),
			@IndexId INT, 
			@DropIndexId INT,/*  Introduce to implement Data Compression. */
            @IsDLCobject BIT = 0,
			@DropExisting BIT = 0,
			@ClusteredFlag BIT = 0,
			@PKFlag  BIT = 0,
			@DropIndexName VARCHAR(1024)

		----------------------------------------------------------------------------------
		-- Normalize Parameters and Parameter Validation
		----------------------------------------------------------------------------------
		BEGIN
		SELECT @SchemaName = MD3.f_RemoveBrackets (@SchemaName);
		SELECT @TableName = MD3.f_RemoveBrackets (@TableName);
		SELECT @IndexName = MD3.f_RemoveBrackets (@IndexName);
		SELECT @FileGroupOrPartition = MD3.f_RemoveBrackets (@FileGroupOrPartition);
		SELECT @IndexKeyColumns = MD3.f_RemoveBrackets (@IndexKeyColumns);
		SELECT @IndexKeyColumns = REPLACE(@IndexKeyColumns,' ','')
		SELECT @IncludedColumns = MD3.f_RemoveBrackets (@IncludedColumns);
		SELECT @IncludedColumns = COALESCE (@IncludedColumns, '')
		SELECT @IncludedColumns = REPLACE(@IncludedColumns,' ','')
		SELECT @DataCompressionType = ISNULL(@DataCompressionType, 'ROW');
		SELECT @DataCompressionType = COALESCE(@DataCompressionType, 'NONE') ;
		SELECT @DataCompressionType = CASE WHEN convert(varchar(200),SERVERPROPERTY ('edition')) LIKE 'Enterprise%' THEN @DataCompressionType ELSE 'NONE' END;


	    IF (@DataCompressionType NOT IN ('ROW', 'PAGE', 'NONE' ))
		BEGIN
			SET @Msg = '@DataCompressionType value is invalid: ' + CAST(@DataCompressionType AS VARCHAR(4)) + '. It must be either ROW/PAGE/NONE.'
			EXEC MD3.InsertLogMessage
				@Message = @Msg, 
				@ProcedureName = 'MD3.CreateIndex', 
				@Severity = 'E';
			RAISERROR(@Msg, 16, 1)
			RETURN 1
		END

		IF MD3.f_TableExists(@SchemaName, @TableName) = 0
		BEGIN
			SET @Msg = 'Table ' + @SchemaName + '.' + @TableName + ' does not exist.'
			EXEC MD3.InsertLogMessage
				@Message = @Msg, 
				@ProcedureName = 'MD3.CreateIndex', 
				@Severity = 'E';
			RAISERROR (@Msg,16,1);
			RETURN 1;
		END;
		END;

		-- if requested to drop the index, do that (if it exists)
		IF (@DropIndex = 1)
		BEGIN
			IF EXISTS (SELECT IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IndexName = @IndexName)
			BEGIN
				
				--Remove Unique Keys.  The MD3 system will always create UNIQUE INDEXES, not UNIQUE CONSTRAINTS.  They
				--are functionally equivalent.  However, the syntax to drop a UNIQUE constraint vs index is very different.
				--we attempt to remove unique constraints here.  
				EXEC MD3.RemoveUniqueConstraints 
					@SchemaName = @SchemaName,
					@TableName = @TableName ,
					@KeyColumns = @IndexKeyColumns;
 
				--doublecheck index existence again.  
				IF EXISTS (SELECT IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IndexName = @IndexName)
				BEGIN
					SELECT @SQL = 'DROP INDEX ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName
					EXEC MD3.ExecuteSQL @SQL;
				END
				RETURN
			END
			ELSE 
			BEGIN
				--index was previously dropped, no work to do.  
				RETURN
			END
		END



        -------------------------------------------------------------------------
		-- does the index exist with the same name and properties already?
		-- log the fact and do nothing
		-------------------------------------------------------------------------
		IF EXISTS(
			SELECT 1
			FROM MD3.f_GetIndexes(@SchemaName,@TableName)
			WHERE
				IndexName = @IndexName AND
				IsPrimaryKey = 0  AND				--we should use MD3.CreatePrimaryKey in this case
				IsClustered = @IsClustered AND
				IsUnique = @IsUnique AND
				IndexKeys = @IndexKeyColumns AND
				IsNull(IncludedColumns, '') = @IncludedColumns AND
				FGorPartitionScheme = @FileGroupOrPartition AND
				IsPadded = @PadIndex AND
				IdxFillFactor = @FillFactor AND
				CompressionSetting = @DataCompressionType
		)
		BEGIN
			EXEC MD3.InsertLogMessage 
				@Message = 'Index already exists as declared.', 
				@Severity = 'I';
			RETURN
		END


		-------------------------------------------------------------------------
		-- If an index with different name but same definition exists
		-- then rename the index
		-------------------------------------------------------------------------
		SELECT @CurrentIndexName = IndexName
		FROM MD3.f_GetIndexes(@SchemaName,@TableName)
		WHERE
			IndexName <> @IndexName AND
			IsPrimaryKey = 0 AND
			IsClustered = @IsClustered AND
			IsUnique = @IsUnique AND
			IndexKeys = @IndexKeyColumns AND
			IsNull(IncludedColumns, '') = @IncludedColumns AND
			FGorPartitionScheme = @FileGroupOrPartition AND
			IsPadded = @PadIndex AND
			IdxFillFactor = @FillFactor AND
			CompressionSetting = @DataCompressionType        

		IF (@CurrentIndexName IS NOT NULL)
		BEGIN
			--Rename the index to new name
			SELECT @SQL = 'sp_rename ''' + @SchemaName + '.' + @TableName + '.' + @CurrentIndexName + ''', ''' + @IndexName+ ''''
			SET @Msg = 'Index ' + @CurrentIndexName + ' already exists with identical properties and a different name. Renaming index to ' + @IndexName+ '.'
			EXEC MD3.InsertLogMessage 
				@Message = @Msg, 
				@Severity = 'I';
			EXEC MD3.ExecuteSQL @SQL;
			RETURN
		END



		-------------------------------------
		--At this point in the code we need to create a new index
		-------------------------------------

		-------------------------------------
		--when creating a clustered index and there is ANY blob column in the table we need to switch to offline mode
		-------------------------------------
		IF (EXISTS(SELECT
			OBJECT_NAME(c.OBJECT_ID) TableName
			,c.name AS ColumnName
			,SCHEMA_NAME(t.schema_id) AS SchemaName
			,t.name AS TypeName
			,t.is_user_defined
			,t.is_assembly_type
			,c.max_length
			,c.PRECISION
			,c.scale
			FROM sys.columns AS c
			JOIN sys.types AS t ON c.user_type_id=t.user_type_id
			AND OBJECT_NAME(c.OBJECT_ID) = @TableName
			AND((c.max_length = -1  and t.name IN ('varchar', 'nvarchar','varbinary'))
			OR(t.name IN ('ntext','text','image','xml')))
			)) AND @IsClustered = 1
		BEGIN
			SELECT @IsOnline = 0
		END

		-------------------------------------
		--when creating a nonclustered index and there is a blob column in the included col list we need to switch to offline mode
		--If we are creating a nonclustered index
		-------------------------------------
		IF @IncludedColumns <> ''
		BEGIN
			IF EXISTS(SELECT
					OBJECT_NAME(c.OBJECT_ID) TableName
					,c.name AS ColumnName
					,SCHEMA_NAME(t.schema_id) AS SchemaName
					,t.name AS TypeName
					,t.is_user_defined
					,t.is_assembly_type
					,c.max_length
					,c.PRECISION
					,c.scale
					FROM sys.columns AS c
					JOIN sys.types AS t ON c.user_type_id=t.user_type_id
					and OBJECT_NAME(c.OBJECT_ID) = @TableName
					and
					((c.max_length = -1  and t.name IN ('varchar', 'nvarchar','varbinary'))
					or
					(t.name IN ('ntext','text','image','xml')))
					join MD3.f_split(@IncludedColumns)
					on Value = c.name
					) AND @IsOnline = 1
			BEGIN
				SELECT @IsOnline = 0
			END
		END

		--ONLINE only works on Enterprise Edition
		IF (@IsOnline = 1 AND convert(varchar(200),SERVERPROPERTY ('edition')) NOT LIKE 'Enterprise%')
		BEGIN
			SELECT @IsOnline = 0 
		END


			--------------------------------------------------------------------------
			--Check for clustered index on the table, and clustered index change,
			--if true, then check for pk modification. if pk is modified, error.
			--if not a pk modification, then drop the existing clustered index and
			--recreate with nonclustered attribute, then create the clustered index requested
			--------------------------------------------------------------------------
			--...if the request is for a clustered index...
			IF @IsClustered = 1
			BEGIN
				--...then handle any existing clustered indexes...we may need to drop/recreate them.  Or...
				IF EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName)
						WHERE IsClustered = 1
					)
				BEGIN
					--Get Current Clustered Index Properties
					SELECT @DropIndexName = ISNULL(IndexName,''), @PKFlag  = ISNULL(IsPrimaryKey,0),
					       @DropIndexId = IndexId 
					FROM MD3.f_GetIndexes(@SchemaName,@TableName)
					WHERE IsClustered = 1

					--Check for PK
					IF @PKFlag  = 1
					BEGIN
						SET @Msg = 'You are attempting to modify a PK clustered index.  This is not allowed. Modify the PK FIRST, then this index for proper stateful object ordering of events.'
						EXEC MD3.InsertLogMessage
							@Message = @Msg, 
							@ProcedureName = 'MD3.CreateIndex', 
							@Severity = 'E';
						RAISERROR (@Msg,16,1);
						RETURN 1;
					END

					--check that we are not just changing the clustering property of this index
					IF @DropIndexName <> @IndexName
					BEGIN
						--Set SQL for recreating this index as a nonclustered index with the same properties
						SELECT @DropIdxDataCompressionType = MD3.f_GetIdxCompressionInfo (@SchemaName,@TableName,@DropIndexName);
						SELECT @SQL2 = ' MD3.CreateIndex ' + '@SchemaName = ' + @SchemaName
											+ ',@TableName = ' + @TableName
											+ ',@IndexName = ' + IndexName
											+ ',@KeyColumns = ''' + IndexKeys + ''''
											+ CASE WHEN IsUnique = 1 THEN ' ,@IsUnique = 1 ' ELSE ' ,@IsUnique = 0 ' END
											+ ',@IsClustered = 0 '
											+ ',@fillfactor = ' + CAST(IdxFillFactor AS Varchar(3))
											+ ',@PadIndex = ' + CAST(ISNULL(IsPadded, 0 )AS Varchar(1))
											+ CASE WHEN @IsDLCobject = 0 THEN ',@ForceOverride = 1, @DataCompressionType = ' + @DropIdxDataCompressionType
											       ELSE ''
											  END
						FROM MD3.f_GetIndexes(@SchemaName,@TableName)
						WHERE IndexName = @DropIndexName 
						AND IsClustered = 1

						EXEC MD3.RemoveUniqueConstraints 
							@SchemaName = @SchemaName,
							@TableName = @TableName ,
							@KeyColumns = @IndexKeyColumns
						
						--need to doublecheck after removing any possible unique constraints
						IF EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IsClustered = 1)
						BEGIN
							--Drop the index
							SET @Msg = 'Index ' + @DropIndexName + ' is currently the clustered index and will be dropped and recreated as a nonclustered '
							SET @Msg = @Msg + 'Index to allow the requested index ' + @IndexName+ ' to be created as the clustered index.'
							SELECT @SQL = ' DROP INDEX '  + @DropIndexName + ' ON ' + @SchemaName + '.' + @TableName + CASE WHEN @IsOnline = 1 THEN ' WITH (ONLINE = ON)' ELSE '' END

							EXEC MD3.InsertLogMessage 
								@Message = @Msg, 
								@Severity = 'I';

							EXEC MD3.ExecuteSQL @SQL;
						END

						--Recreate the index as nonclustered
						SET @Msg = 'Index ' + @DropIndexName + ' has been recreated as a nonclustered index.'
						EXEC MD3.InsertLogMessage 
								@Message = @Msg, 
								@Severity = 'I';
						EXEC MD3.ExecuteSQL @SQL2;
					END
				END

				--...find any nci's that may need to be dropped and recreated because they match the requested clustered index
				IF EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName)
						WHERE IsClustered = 0 AND IndexName = @IndexName
					)
				BEGIN
					EXEC MD3.RemoveUniqueConstraints 
						@SchemaName = @SchemaName,
						@TableName = @TableName ,
						@KeyColumns = @IndexKeyColumns;
					--and doublecheck again
					IF EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName)
							WHERE IsClustered = 0 AND IndexName = @IndexName
						)
					BEGIN
						--Drop the index
						SELECT @SQL = ' DROP INDEX '  + @IndexName + ' ON ' + @SchemaName + '.' + @TableName
						SET @Msg = 'Index ' + @IndexName  + ' is currently NonClustered index and will be dropped to recreate as a Clustered.'
						
						EXEC MD3.InsertLogMessage 
								@Message = @Msg, 
								@Severity = 'I';
						EXEC MD3.ExecuteSQL @SQL;

						SELECT @DropExisting = 0
					END
				END
			END
			ELSE
			--...or the current request is for a nci but the index name is currently clustered
			IF(EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IsClustered = 1 AND IndexName = @IndexName AND @IsClustered = 0))
			BEGIN
				EXEC MD3.RemoveUniqueConstraints 
						@SchemaName = @SchemaName,
						@TableName = @TableName ,
						@KeyColumns = @IndexKeyColumns;

				--and doublecheck again
				IF(EXISTS(SELECT 1 FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IsClustered = 1 AND IndexName = @IndexName AND @IsClustered = 0 ))
				BEGIN
					--Drop the index
					SELECT @SQL = ' DROP INDEX '  + @IndexName + ' ON ' + @SchemaName + '.' + @TableName + CASE WHEN @IsOnline = 1 THEN ' WITH (ONLINE = ON)' ELSE '' END
					SET @Msg = 'Index ' + @IndexName  + ' is currently Clustered index and will be dropped to recreate as a Nonclustered.'

					EXEC MD3.InsertLogMessage 
						@Message = @Msg, 
						@Severity = 'I';
					EXEC MD3.ExecuteSQL @SQL;

					EXEC(@SQL)

					SELECT @DropExisting = 0
				END
			END

			---------------------------
			--Set Drop Existing Flag
			---------------------------
			IF EXISTS(SELECT IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IndexName = @IndexName AND IsClustered = @IsClustered )
			BEGIN
				EXEC MD3.RemoveUniqueConstraints 
					@SchemaName = @SchemaName,
					@TableName = @TableName ,
					@KeyColumns = @IndexKeyColumns;

				IF EXISTS(SELECT IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IndexName = @IndexName AND IsClustered = @IsClustered)
				BEGIN
					SELECT @DropExisting = 1
				END
			END



		--------------------------------------------------
		--Create the index
		--------------------------------------------------

		SELECT @SQL = ''

		SELECT @SQL = @SQL + 'SET QUOTED_IDENTIFIER ON; CREATE ' + CASE WHEN @IsUnique = 1 THEN 'UNIQUE ' ELSE '' END
		SELECT @SQL = @SQL + CASE WHEN @IsClustered = 1 THEN  ' CLUSTERED ' ELSE ' NONCLUSTERED ' END  + ' INDEX ' + @IndexName+ ' ON ' + @SchemaName + '.'
		SELECT @SQL = @SQL + @TableName + ' (' + REPLACE(@IndexKeyColumns,'(-)',' DESC') + ')'
		SELECT @SQL = @SQL + CASE WHEN @IncludedColumns <> '' THEN ' INCLUDE (' + @IncludedColumns + ')' ELSE '' END
		SELECT @SQL = @SQL + ' WITH( '
		SELECT @SQL = @SQL + CASE WHEN @FillFactor > 0 THEN 'FILLFACTOR = ' + CAST(@FillFactor AS VARCHAR(3)) + ', ' ELSE '' END
		SELECT @SQL = @SQL + CASE WHEN @PadIndex = 1 THEN ' PAD_INDEX = ON'
									ELSE ' PAD_INDEX = OFF' END
		SELECT @SQL = @SQL + ', SORT_IN_TEMPDB = ON'
		SELECT @SQL = @SQL + ', IGNORE_DUP_KEY = OFF' -- Produce an error if try to insert a duplicate key value
		SELECT @SQL = @SQL + ', STATISTICS_NORECOMPUTE = OFF' -- Auto statistics turned on
		SELECT @SQL = @SQL +	CASE  WHEN @DropExisting = 1 THEN  ', DROP_EXISTING = ON'
								ELSE '' END
		SELECT @SQL = @SQL + CASE WHEN @IsOnline = 1 THEN ', ONLINE = ON' ELSE '' END
		SELECT @SQL = @SQL + ', ALLOW_ROW_LOCKS = ON'
		SELECT @SQL = @SQL + ', ALLOW_PAGE_LOCKS = ON'
		SELECT @SQL = @SQL +  ', MAXDOP = 8'
		SELECT @SQL = @SQL + ', DATA_COMPRESSION = ' + @DataCompressionType
		SELECT @SQL = @SQL + ')'
		SELECT @SQL = @SQL + ' ON ' + CASE WHEN @FileGroupOrPartition = 'PRIMARY' THEN '[PRIMARY]' ELSE @FileGroupOrPartition END

		EXEC MD3.ExecuteSQL @SQL;


END
GO